{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''1.导入pandas库'''\n",
    "import pandas as pd\n",
    "\n",
    "'''2.导入文件'''\n",
    "#设置文件名称\n",
    "file=\"运费明细表.xlsx\"\n",
    "df = pd.read_excel(file,sheet_name='运费明细',skiprows=3,header=2,usecols='C:F',dtype={'ID':object,'账单日期':str,'出口日期':str},index_col='ID')\n",
    "#skiprows 跳过前3行\n",
    "#header 从第3行开始读\n",
    "#usecols 列选择C到F列数据读取\n",
    "#dtype 重点 pandas把NaN默认flode 如果想下面迭代表达先把空列设置成str类型、或者object\n",
    "#index_col 把ID列作为DateFrame的index列\n",
    "#sheet_name=‘运费明细’ 把Excel表中读取运费明细表 或者sheet2\n",
    "\n",
    "'''3.导入CSV,TSV,TXT文件中的数据'''\n",
    "##导入csv文件\n",
    "df = pd.read_csv(file, index_col='ID')\n",
    "##导入Tsv文件，文件中带有\\t分隔符\n",
    "df = pd.read_csv(file, sep='\\t', index_col='ID')\n",
    "##导入TXT文件，文件中带有'|'分隔符\n",
    "df = pd.read_csv(file, sep='|', index_col='ID')\n",
    "#读取文件endcoding为简体中文格式，从第二行开始读\n",
    "df = pd.read_csv(file,encoding='gb18030',header=1) \n",
    "\n",
    "\n",
    "'''4.保存文件'''\n",
    "##保存为EXE文件\n",
    "df.to_excel(file)\n",
    "##保存为CSV文件\n",
    "df.to_csv(file,encoding='gb18030')\n",
    "#如果未设置index列系统自动保存，excel打开后 多出一列index，解决方案指定index列\n",
    "#方法：\n",
    "df=df.set_index('ID')\n",
    "df.set_index('ID'，inplace=True)\n",
    "##或者\n",
    "with pd.ExcelWriter(file7, mode='a',engine='openpyxl') as writer:\n",
    "    df20.to_excel(writer,sheet_name='I202101',index=False)  #新开一工作表I01而保存文件\n",
    "\n",
    "'''5.排序多重排序'''\n",
    "#先以 账单日期按顺序排序，再以出口日期按倒序排序\n",
    "#ascending True 从小到大\n",
    "#inplace True 直接在df数据上保留修改\n",
    "df.sort_values(by=['账单日期', '出口日期'], ascending=[True,False], inplace=True)\n",
    "##ascending False：倒序,从大到小\n",
    "df.sort_values(by='出口日期',inplace=True,ascending=False)\n",
    "\n",
    "\n",
    "'''6.数据查看'''\n",
    "#查看每列数据数据类型 str time object等等\n",
    "df.info()\n",
    "#查看头部前三行\n",
    "df.head(3)\n",
    "#查看尾部\n",
    "df.tail()\n",
    "\n",
    "\n",
    "'''7.多表合并'''\n",
    "##df1为需合并表格，df2为被合并表格，将两表合并\n",
    "table = df1.merge(df2,how='left',on='ID').fillna('没找到')\n",
    "##或者\n",
    "table=pd.merge(df1,df2,on='ID')\n",
    "#how=’left‘ 表示依 df1 基础 保留所有df1列信息。默认inner参数\n",
    "#on=’ID‘ df1与df2都有ID列 前提两张表都有ID列，没有用 left_on与right_on\n",
    "#.fillna() 表示 在df1中df2没有的数据填下’没找到‘\n",
    "#merge() 不能默认指定index列 必须 on指定\n",
    "#how='outer'外连接：并集\n",
    "#how='inner\"内连接：交集\n",
    "#how='left'左连接：左边对象全部保留\n",
    "#how='right'右连接：右边对象全部保留\n",
    "\n",
    "#用join 合并指定表格,因为没有公共值，因此联接操作失败，\n",
    "#因为这些值不重叠--它要求为左侧和右侧提供一个后缀：lsuffix='_left', rsuffix='_right'\n",
    "table = df1.join(df2, how='left').fillna(0)\n",
    "\n",
    "\n",
    "'''8.分类汇总'''\n",
    "#分类方法：\n",
    "groups = df.groupby(['账单日期', '出口日期'])\n",
    "#根据列分组\n",
    "s = groups['应收费用'].sum()\n",
    "#ID列求计数\n",
    "c = groups['ID'].count()\n",
    "#两两合并\n",
    "df2 = pd.DataFrame({'Sum': s, 'Count': c})\n",
    "#s、c 两个DataFrame 按照列Sum 与 Count 再合并成一个新DateFrame\n",
    "\n",
    "\n",
    "'''9.消除重复数据'''\n",
    "##将账单号码这一列重复的消除，并保留第一个,其参数为keep='first'\n",
    "##当keep='last'时就是保留最后一次出现的重复行。 \n",
    "##inplace=True它将从原始DataFrame中删除所有重复。\n",
    "df=df.drop_duplicates(subset='账单号码', inplace=True, keep='first')\n",
    "print(df)\n",
    "\n",
    "\n",
    "'''10.删除行列方法'''\n",
    "#删除ID，账单日期两列\n",
    "df.drop([\"ID\",\"账单日期\"],axis =1)\n",
    "##或\n",
    "df.drop(columns = [\"ID\",\"账单日期\"])\n",
    "##删除行的方法\n",
    "df.drop([\"0a\",\"1b\"],axis = 0)\n",
    "#或\n",
    "df.drop(df.index[[0,1]])\n",
    "##删除特定的行，将ID大于40的行列出来\n",
    "df[df[\"ID\"]<40]\n",
    "\n",
    "\n",
    "'''11.旋转数据表'''\n",
    "##行列转换\n",
    "table = df.transpose()\n",
    "##智能将行列转秩\n",
    "df=df.T\n",
    "\n",
    "'''12.列与列的运算'''\n",
    "##算术相加\n",
    "df=df[\"实际重量\"]+df[\"计费重量\"]\n",
    "##相减\n",
    "df=df[\"实际重量\"]-df[\"计费重量\"]\n",
    "##相乘\n",
    "df=df[\"实际重量\"]*df[\"计费重量\"]\n",
    "##相除\n",
    "df=df[\"实示重量\"]/df[\"计费重量\"]\n",
    "##求平均\n",
    "df=df[\"实示重量\"].mean()\n",
    "##求合计\n",
    "df=df[\"实示重量\"].sum()\n",
    "\n",
    "'''13.单列折分'''\n",
    "##split的意思是折分，‘：’是折份的标准，一列变成两列\n",
    "df = df['运单号（长）'].str.split('：', expand=True)\n",
    "##或者用自动义函数\n",
    "def split_func(line):\n",
    "    line[\"运单号\"], line[\"长\"] = line[\"运单号（长）\"].split(\":\")\n",
    "    return line\n",
    "df = df.apply(split_func, axis=1)\n",
    "\n",
    "'''14.将NAN值变成0'''\n",
    "df16 = df16.fillna(0)#将NAN值变成0\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
